package messages;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ListServlet extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException 
	{
		
//		System.out.println("RemoteAddr" + req.getRemoteAddr());
//		System.out.println("RemoteHost" + req.getRemoteHost());
//		System.out.println("RemotePort" + req.getRemotePort());
//		System.out.println("RemoteUser" + req.getRemoteUser());
//		System.out.println("LocalName" + req.getLocalName());
//		System.out.println("LocalAddr" + req.getLocalAddr());
//		System.out.println("LocalPort" + req.getLocalPort());
		
		perform(req, resp);
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		perform(req, resp);
	}

	private void perform(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException 
	{		
		//	文字列定数
		final String CNT_SQL = "select count(*) as cnt from messages";
		final String SQL = "select * from messages "
									+ "where delete_flag = 0 "
									+ "order by id desc "
									+ "limit ?, 5";
		//	変数の宣言・初期化
		int max = 0;																	//	全レコード数
		ArrayList<Message> list = new ArrayList<Message>();	//	一覧データ
		//	パラメータを取得
		req.setCharacterEncoding("UTF-8");
		String start = req.getParameter("start");						//	頁開始行
		if (start == null || start.length() == 0) {
			start = "0";
		}

		try {
			Connection con = DBManager.getConnection();			//	DB接続
			Statement stm = con.createStatement();					//	SQL準備
			//	全レコード数を取得
			ResultSet cntRs = stm.executeQuery(CNT_SQL);
			if (cntRs.next()) {
				max = cntRs.getInt("cnt");
			}
			cntRs.close();
			//	一覧データを準備
			PreparedStatement ps = con.prepareStatement(SQL);//	SQL準備
			ps.setInt(1, Integer.parseInt(start));
			ResultSet rs = ps.executeQuery();								//	テーブルを検索
			//	全ての検索結果について処理を繰り返す
			while (rs.next()) {
				//	Messageオブジェクトを作成
				Message m = new Message();
				m.setId( rs.getInt( "id" ) );
				m.setAccount( rs.getString( "account" ) );
				m.setMessage( rs.getString( "message") );
				m.setUserAgent(rs.getString("user_agent"));
				m.setDelete_flag(rs.getString( "delete_flag"));
				m.setRegist_at(rs.getString("regist_at"));
				//	一覧データに追加
				list.add(m);
			}
			rs.close();																	//	後処理
			stm.close();
			con.close();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}

		req.setAttribute("max", new Integer(max));					//	全レコード数
		req.setAttribute("list", list);											//	一覧データ

		RequestDispatcher d =													//	画面遷移する
				req.getRequestDispatcher("list.jsp");
		d.forward(req, resp);
	}
}
